import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.json.JSONObject;
import org.json.JSONArray;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class ExcelToXmlConverter {

    public static void main(String[] args) {
        try {
//            // 示例调用
//            convertExcelToXml("数据库系统成绩登记表.xlsx", "MyStudentInfo.xml");
//            convertExcelToJson("数据库系统成绩登记表.xlsx", "MyStudentInfo.json");
            manualConvert();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 手动输入调用方法
    public static void manualConvert() {
        Scanner scanner = new Scanner(System.in);

        try {
            System.out.println("\n==== Excel转换工具 ====");
            System.out.println("请选择转换类型:");
            System.out.println("1. Excel → XML");
            System.out.println("2. Excel → JSON");
            System.out.println("3. Excel → XML 和 JSON");
            System.out.print("输入选择 (1-3): ");

            int choice = scanner.nextInt();
            scanner.nextLine(); // 清除换行符

            if (choice < 1 || choice > 3) {
                System.out.println("无效选择，程序结束。");
                return;
            }

            //数据库系统成绩登记表.xlsx
            System.out.print("请输入Excel文件路径 (默认: 数据库系统成绩登记表.xlsx): ");
            String inputFilePath = scanner.nextLine().trim();
            if (inputFilePath.isEmpty()) {
                inputFilePath = "数据库系统成绩登记表.xlsx";
                System.out.println("输入为空，Excel文件为默认：数据库系统成绩登记表.xlsx");
            }

            // 检查文件是否存在
            File inputFile = new File(inputFilePath);
            if (!inputFile.exists() || !inputFile.isFile()) {
                System.out.println("错误: 文件不存在或路径无效: " + inputFilePath);
                return;
            }

            switch (choice) {
                case 1: // XML
                    System.out.print("请输入XML输出文件路径 (默认: MyStudentInfo.xml): ");
                    String xmlPath = scanner.nextLine().trim();
                    if (xmlPath.isEmpty()) {xmlPath = "MyStudentInfo.xml";System.out.println("输入为空，xml文件为默认：MyStudentInfo.xml");}
                    convertExcelToXml(inputFilePath, xmlPath);
                    break;

                case 2: // JSON
                    System.out.print("请输入JSON输出文件路径 (默认: MyStudentInfo.json): ");
                    String jsonPath = scanner.nextLine().trim();
                    if (jsonPath.isEmpty()) jsonPath = "MyStudentInfo.json";
                    convertExcelToJson(inputFilePath, jsonPath);
                    break;

                case 3: // XML 和 JSON
                    System.out.print("请输入XML输出文件路径 (默认: MyStudentInfo.xml): ");
                    String xmlOutput = scanner.nextLine().trim();
                    if (xmlOutput.isEmpty()) xmlOutput = "MyStudentInfo.xml";

                    System.out.print("请输入JSON输出文件路径 (默认: MyStudentInfo.json): ");
                    String jsonOutput = scanner.nextLine().trim();
                    if (jsonOutput.isEmpty()) {jsonOutput = "MyStudentInfo.json";System.out.println("输入为空，json文件为默认：MyStudentInfo.json");}

                    convertExcelToXml(inputFilePath, xmlOutput);
                    convertExcelToJson(inputFilePath, jsonOutput);
                    break;
            }

            System.out.println("\n转换操作完成!");
        } catch (Exception e) {
            System.err.println("转换过程中出错: " + e.getMessage());
            e.printStackTrace();
        } finally {
        }
    }

    // 1. Excel转XML方法
    public static void convertExcelToXml(String inputFilePath, String outputFilePath) throws Exception {
        List<Student> students = readExcelData(inputFilePath);
        addProjectMembers(students);
        generateXmlFile(students, outputFilePath);
        System.out.println("XML转换成功: " + outputFilePath);
    }

    // 2. Excel转JSON方法
    public static void convertExcelToJson(String inputFilePath, String outputFilePath) throws Exception {
        List<Student> students = readExcelData(inputFilePath);
        addProjectMembers(students);
        generateJSONFile(students, outputFilePath);
        System.out.println("JSON转换成功: " + outputFilePath);
    }

    // 读取Excel数据的通用方法（带文件路径参数）
    private static List<Student> readExcelData(String inputFilePath) throws Exception {
        List<Student> students = new ArrayList<>();
        try (InputStream fis = new FileInputStream(new File(inputFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);

            // 1. 查找标题行
            int titleRowIndex = -1;
            String[] expectedTitles = {"序号", "学号", "姓名", "性别", "主修专业"};

            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                boolean isTitleRow = true;
                for (int col = 0; col < expectedTitles.length; col++) {
                    String cellValue = getCellValue(row.getCell(col));
                    if (!expectedTitles[col].equals(cellValue)) {
                        isTitleRow = false;
                        break;
                    }
                }

                if (isTitleRow) {
                    titleRowIndex = rowNum;
                    break;
                }
            }

            // 如果找不到标题行，报错退出
            if (titleRowIndex == -1) {
                throw new Exception("未找到标题行（应包含：序号、学号、姓名、性别、主修专业）");
            }

            // 2. 从标题行的下一行开始读取数据
            int startRow = titleRowIndex + 1;

            for (; startRow <= sheet.getLastRowNum(); startRow++) {
                Row row = sheet.getRow(startRow);
                if (row == null) continue;
                if(!expectedTitles[0].equals(getCellValue(row.getCell(0)))&&getCellValue(row.getCell(0))!="") {
                    break;
                }
            }
            // 3. 读取数据行
            for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 检查是否到达末尾（首个单元格为空）
                Cell firstCell = row.getCell(0);
                if (firstCell == null || firstCell.getCellType() == CellType.BLANK ||
                        getCellValue(firstCell).isEmpty()) {
                    break;
                }

                // 解析数据列
                String id = getCellValue(row.getCell(1));      // 学号（标题后第2列）
                String name = getCellValue(row.getCell(2));    // 姓名
                String gender = getCellValue(row.getCell(3));  // 性别
                String major = getCellValue(row.getCell(4));   // 主修专业

                if (id != null && !id.isEmpty()) {
                    students.add(new Student(id, name, gender, major));
                }
            }
        } catch (Exception e) {
            throw new Exception("解析Excel失败：" + e.getMessage(), e);
        }
        return students;
    }

    // get cell value (保持不变)
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue().trim();
            case NUMERIC -> String.valueOf((int) cell.getNumericCellValue());
            default -> "";
        };
    }

    // add our data (保持不变)
    private static void addProjectMembers(List<Student> students) {
        students.add(new Student("2022150031", "段雨龙", "男", "软件工程"));
        students.add(new Student("2022150098", "黄奕程", "男", "软件工程"));
        students.add(new Student("2022150195", "简伟鹏", "男", "软件工程"));
        students.add(new Student("2022150150", "喻子聪", "男", "软件工程"));
    }

    // gen XML (增加输出路径参数)
    private static void generateXmlFile(List<Student> students, String outputFilePath) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.newDocument();
        // root <Students>
        Element root = doc.createElement("Students");
        doc.appendChild(root);
        // student
        for (Student student : students) {
            Element studentElem = doc.createElement("Student");
            root.appendChild(studentElem);
            addElement(doc, studentElem, "ID", student.getId());
            addElement(doc, studentElem, "Name", student.getName());
            addElement(doc, studentElem, "Gender", student.getGender());
            addElement(doc, studentElem, "Major", student.getMajor());
        }
        // output
        TransformerFactory transformerFactory = TransformerFactory.newInstance();
        Transformer transformer = transformerFactory.newTransformer();
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");

        DOMSource source = new DOMSource(doc);
        StreamResult result = new StreamResult(new File(outputFilePath));
        transformer.transform(source, result);
    }

    // add XML element (保持不变)
    private static void addElement(Document doc, Element parent, String tagName, String value) {
        Element elem = doc.createElement(tagName);
        elem.appendChild(doc.createTextNode(value));
        parent.appendChild(elem);
    }

    // gen JSON (增加输出路径参数)
    private static void generateJSONFile(List<Student> students, String outputFilePath) throws Exception {
        JSONArray jsonArray = new JSONArray();
        for(Student student : students) {
            JSONObject studentObj = new JSONObject();
            studentObj.put("ID", student.getId());
            studentObj.put("Name", student.getName());
            studentObj.put("Gender", student.getGender());
            studentObj.put("Major", student.getMajor());
            jsonArray.put(studentObj);
        }
        // final JSON
        JSONObject finalJson = new JSONObject();
        finalJson.put("Students", jsonArray);
        // output file
        try (FileWriter file = new FileWriter(outputFilePath)) {
            file.write(finalJson.toString(2));
        }
    }
}

